﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections.Generic;
using System.Web.Management;

namespace dao
{
    public class BaseDao : IDao
    {
        protected SqlConnection Conexion = new SqlConnection();
        protected SqlCommand Cmd = new SqlCommand();
        protected SqlTransaction Transaccion = null;

        public string StringConexion = WebConfigurationManager.ConnectionStrings["StringConexionNico"].ConnectionString;

        protected string NombreTabla;
        protected string NombrePk;
        protected bool PkAutonumerica;

        public BaseDao(string _nombreTabla, string _nombrePk, bool _pkAutonumerica)
        {
            NombreTabla = _nombreTabla;
            NombrePk = _nombrePk;
            PkAutonumerica = _pkAutonumerica;
        }

        public BaseDao()
        {
        }

        //METODOS DE SOPORTE GENERALES
        protected void Conectar()
        {
            if (Conexion.State == ConnectionState.Open)
            {
                Conexion.Close();
            }
            Conexion.ConnectionString = StringConexion;
            Conexion.Open();
            Cmd.Connection = Conexion;
            Cmd.CommandType = CommandType.Text;
        }

        public void Insertar(string[] valores)
        {

        }

        private DataTable EjecutarReader(string consulta)
        {
            DataTable tabla = new DataTable();

            Conectar();
            Cmd.CommandText = consulta;
            tabla.Load(Cmd.ExecuteReader(CommandBehavior.CloseConnection));
            Conexion.Close();

            return tabla;
        }


        public SqlDataReader traerDatosConsulta(string consulta)
        {
            Conectar();
            SqlDataReader dr = null;
            try
            {
                Cmd.CommandText = consulta;
                dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
            catch (Exception)
            {
                return dr;
            }

        }

        private void EjecutarNonQuery(string consulta)
        {
            Conectar();
            Cmd.CommandText = consulta;
            Cmd.ExecuteNonQuery();
            Conexion.Close();
        }

        private DataTable LeoEstructura()
        {
            string comando = null;
            DataTable tabla = new DataTable();
            comando = "select top 1 * from " + NombreTabla;
            tabla = EjecutarReader(comando);
            return tabla;
        }


        //METODOS BASE
        public SqlDataReader ObtenerTodo()
        {
            Conectar();
            SqlDataReader dr = null;
            Cmd.CommandText = "select * from " + NombreTabla;
            //Cmd.Parameters.Add(new SqlParameter("@nombreTabla", NombreTabla));
            dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return dr;
        }

        public DataTable ObtenerPorId(int id)
        {
            Conectar();
            SqlDataReader dr = null;
            DataTable dt = new DataTable();
            try
            {
                //Cmd.CommandText = "SELECT * FROM @nombreTabla WHERE @nombrePk = @id";
                //Cmd.Parameters.Add(new SqlParameter("@nombreTabla", NombreTabla));
                //Cmd.Parameters.Add(new SqlParameter("@nombrePk", NombrePk));
                //Cmd.Parameters.Add(new SqlParameter("@id", id));
                //dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
                Cmd.CommandText = "SELECT * FROM " + NombreTabla +  " WHERE " + NombrePk  + " = "  + id;
                dt.Load(Cmd.ExecuteReader());
                Conexion.Close();
                return dt;
            }
            catch (Exception e)
            {
                throw new SqlExecutionException("Error al obtener por Id BaseDao", e);
            }
        }

        public bool Existe(int pk)
        {
            Conectar();
            try
            {
                Cmd.CommandText = "select * from @nombreTabla where @nombrePk = @pk";
                Cmd.Parameters.Add(new SqlParameter("@nombreTabla", NombreTabla));
                Cmd.Parameters.Add(new SqlParameter("@nombrePk", NombrePk));
                Cmd.Parameters.Add(new SqlParameter("@pk", pk));
                SqlDataReader dr = Cmd.ExecuteReader();
                if (dr.Read())
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new SqlExecutionException("Error al verificar si existe", e);
            }
            finally
            {
                Conexion.Close();
            }
        }



        public void Eliminar(int pk)
        {
            Conectar();
            try
            {
                Cmd.CommandText = "DELETE FROM " + NombreTabla + " WHERE " + NombrePk + " = " + pk;
                Cmd.Parameters.Add(new SqlParameter("@nombreTabla", NombreTabla));
                Cmd.Parameters.Add(new SqlParameter("@nombrePk", NombrePk));
                Cmd.Parameters.Add(new SqlParameter("@pk", pk));
                Cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new SqlExecutionException("Error al eliminar", e);
            }
            finally
            {
                Conexion.Close();
            }
        }


        public void Insertar(string query)
        {
            this.Conectar();
            this.Cmd.CommandText = query;
            Cmd.ExecuteNonQuery();
            this.Conexion.Close();
        }

        public void Insertar(string query, SqlConnection con, SqlTransaction trans)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Transaction = trans;
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = query;
            cmd.ExecuteNonQuery();
        }

        public void Modificar(string updateQuery)
        {
            Conectar();
            Cmd.CommandText = updateQuery;
            Cmd.ExecuteNonQuery();
            Conexion.Close();
        }
    }
}
